package cn.hl.hm.util;

import cn.hl.hm.constants.DriverStatus;
import cn.hl.hm.constants.DriverWorkStatus;
import cn.hl.hm.constants.WithrawalStatus;
import cn.hl.hm.entity.RechargeRecoard;
import cn.hl.hm.entity.RechargeRecoardDo;
import cn.hl.hm.entity.RoadFee;
import cn.hl.hm.entity.WithdrawApply;
import cn.hl.hm.vo.DriverDetailVo;
import cn.hl.hm.vo.RechargeRecoardOfflineVo;
import cn.hl.hm.vo.RechargeRecoardWithdrawalVo;
import cn.hl.hm.vo.VehicleVo;
import com.vizhuo.utils.DateUtil;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * 导出excel
 * 
 * @author 星矢
 *
 */
public class ExportData {

	
	private final static SimpleDateFormat hhmmSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static void exportDriverAccountExcel(String fileName, List<RechargeRecoard> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 10); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);  
			ws.addCell(new Label(1, 0, "用户名", wcf));
			ws.addCell(new Label(2, 0, "订单到账号", wcf));
			ws.addCell(new Label(3, 0, "期初账户余额", wcf));
			ws.addCell(new Label(4, 0, "充值", wcf));
			ws.addCell(new Label(5, 0, "奖励", wcf));
			ws.addCell(new Label(6, 0, "保全金", wcf));
			ws.addCell(new Label(7, 0, "支付配载费", wcf));
			ws.addCell(new Label(8, 0, "退款", wcf));
			ws.addCell(new Label(9, 0, "期末账户余额", wcf));
	 
		 

//			// 填充数据的内容
			RechargeRecoard[] p = new RechargeRecoard[list.size()];
			for (int i = 0; i < list.size(); i++) {
				p[i] = (RechargeRecoard) list.get(i);
				ws.addCell(new Label(1, i + 1, p[i].getDriverName(), wcf));
				ws.addCell(new Label(2, i + 1, p[i].getDriverAccount(), wcf));
				if(p[i].getBeginAmount()!=null){
					ws.addCell(new Label(3, i + 1,  p[i].getBeginAmount(), wcf));
				}else{
					ws.addCell(new Label(3, i + 1,  "", wcf));
				}
				if(p[i].getRechageAmount()!=null){
					ws.addCell(new Label(4, i + 1, p[i].getRechageAmount().toString(), wcf));
				}else{
					ws.addCell(new Label(4, i + 1, "", wcf));
				}

				if(p[i].getGiftAmount()!=null){
					ws.addCell(new Label(5, i + 1, p[i].getGiftAmount(), wcf));
				}else{
					ws.addCell(new Label(5, i + 1, "", wcf));
				}
				if(p[i].getSecurity()!=null){
					ws.addCell(new Label(6, i + 1, p[i].getSecurity().toString(), wcf));
				}else{
					ws.addCell(new Label(6, i + 1,"", wcf));
				}
				if(p[i].getTotalConsume()!=null){
					ws.addCell(new Label(7, i + 1, p[i].getTotalConsume().toString(), wcf));
				}else{
					ws.addCell(new Label(7, i + 1,"", wcf));
				}
				if(p[i].getTotalTK()!=null){
					ws.addCell(new Label(8, i + 1, p[i].getTotalTK().toString(), wcf));
				}else{
					ws.addCell(new Label(8, i + 1,"", wcf));
				}
				if(p[i].getEndAmount()!=null){
						ws.addCell(new Label(9, i + 1, p[i].getEndAmount(), wcf));
					}else{
						ws.addCell(new Label(9, i + 1, "", wcf));
				}
				if (i == 0)
					wcf = new WritableCellFormat();
			}
			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}






	public static void exportRoadFeeExcel(String fileName, List<RoadFee> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 10); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(1, 0, "时间", wcf));
			ws.addCell(new Label(2, 0, "订单号", wcf));
			ws.addCell(new Label(3, 0, "收费类型", wcf));
			ws.addCell(new Label(4, 0, "车辆类型", wcf));
			ws.addCell(new Label(5, 0, "车牌", wcf));
			ws.addCell(new Label(6, 0, "价格", wcf));
			ws.addCell(new Label(7, 0, "是否满载", wcf));
			ws.addCell(new Label(8, 0, "收费方式", wcf));
			ws.addCell(new Label(9, 0, "收费状态", wcf));
			ws.addCell(new Label(10, 0, "免费原因", wcf));
			ws.addCell(new Label(11, 0, "手机号", wcf));
			ws.addCell(new Label(12, 0, "收费员姓名", wcf));




//			// 填充数据的内容
			RoadFee[] p = new RoadFee[list.size()];
			for (int i = 0; i < list.size(); i++) {
				p[i] = (RoadFee) list.get(i);
				ws.addCell(new Label(1, i + 1, hhmmSdf.format(p[i].getCreateTime()), wcf));
				ws.addCell(new Label(2, i + 1, p[i].getOrderCode(), wcf));
				if(p[i].getCarFeeType()!=null){
					ws.addCell(new Label(3, i + 1,  p[i].getCarFeeType(), wcf));
				}else{
					ws.addCell(new Label(3, i + 1,  "", wcf));
				}
				if(p[i].getCarNarrowType()!=null){
					ws.addCell(new Label(4, i + 1, p[i].getCarNarrowType(), wcf));
				}else{
					ws.addCell(new Label(4, i + 1, "", wcf));
				}

				if(p[i].getPlateNumber()!=null){
					ws.addCell(new Label(5, i + 1, p[i].getPlateNumber(), wcf));
				}else{
					ws.addCell(new Label(5, i + 1, "", wcf));
				}

				if(p[i].getTotalPrice()!=null){
					ws.addCell(new Label(6, i + 1, p[i].getTotalPrice().toString(), wcf));
				}else{
					ws.addCell(new Label(6, i + 1,"", wcf));
				}
				if(p[i].getIsFullLoad()!=null){
					if(p[i].getIsFullLoad().equals("1")){
						ws.addCell(new Label(7, i + 1, "否", wcf));
					}else{
						ws.addCell(new Label(7, i + 1, "是", wcf));
					}
				}

				//21、微信扫商家  22、微信扫用户  23、支付宝扫商家   24、支付宝扫用户 25、余额 26、现金
				if(p[i].getPayType()!=null){
					if(p[i].getPayType().equals("21")){
						ws.addCell(new Label(8, i + 1, "微信扫商家", wcf));
					}else if (p[i].getPayType().equals("22")){
						ws.addCell(new Label(8, i + 1, "微信扫用户", wcf));
					}else if (p[i].getPayType().equals("23")){
						ws.addCell(new Label(8, i + 1, "支付宝扫商家", wcf));
					}else if (p[i].getPayType().equals("24")){
						ws.addCell(new Label(8, i + 1, "支付宝扫用户", wcf));
					}else if (p[i].getPayType().equals("25")){
						ws.addCell(new Label(8, i + 1, "余额", wcf));
					}else{
						ws.addCell(new Label(8, i + 1, "现金", wcf));
					}
				}else{
				ws.addCell(new Label(8, i + 1, "", wcf));
			}
				//1.待付款
				//2.已付款
				if (p[i].getOrderStatus().equals("1")) {
					ws.addCell(new Label(9, i + 1, "待付款", wcf));
				} else   if(p[i].getOrderStatus().equals("2"))  {
					ws.addCell(new Label(9, i + 1, "已付款", wcf));
				}else{
					ws.addCell(new Label(9, i + 1, "已关闭", wcf));
				}
				// 2IOS／3Android／4微信／0PC
				ws.addCell(new Label(10, i + 1, p[i].getFreeReason(), wcf));
				ws.addCell(new Label(11, i + 1, p[i].getDriverAccount(), wcf));
				if(p[i].getCollectorName()!=null){
					ws.addCell(new Label(12, i + 1, p[i].getCollectorName(), wcf));
				}else{
					ws.addCell(new Label(12, i + 1, "", wcf));
				}
				if (i == 0)
					wcf = new WritableCellFormat();
			}

			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}


	public static void exportRechargeExcel(String fileName, List<RechargeRecoard> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 10); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);

			// 时间	充值手机号	用户名	充值订单号	充值金额	奖励政策	奖励金额	余额	充值方式	线下充值账号
			ws.addCell(new Label(1, 0, "时间", wcf));
			ws.addCell(new Label(2, 0, "充值手机号", wcf));
			ws.addCell(new Label(3, 0, "用户名", wcf));
			ws.addCell(new Label(4, 0, "充值订单号", wcf));
			ws.addCell(new Label(5, 0, "充值金额", wcf));
			ws.addCell(new Label(6, 0, "奖励政策", wcf));
			ws.addCell(new Label(7, 0, "奖励金额", wcf));
			ws.addCell(new Label(8, 0, "余额", wcf));
			ws.addCell(new Label(9, 0, "充值方式", wcf));
			ws.addCell(new Label(10, 0, "线下充值账号", wcf));
			ws.addCell(new Label(11, 0, "是否开票", wcf));

//			// 填充数据的内容
			RechargeRecoard[] p = new RechargeRecoard[list.size()];
			for (int i = 0; i < list.size(); i++) {
				p[i] = (RechargeRecoard) list.get(i);
				ws.addCell(new Label(1, i + 1, p[i].getCreateTime().toLocaleString(), wcf));
				if(p[i].getUserphone()!=null){
						ws.addCell(new Label(2, i + 1, p[i].getUserphone(), wcf));
				}else{
					ws.addCell(new Label(2, i + 1, "", wcf));
				}


				if(p[i].getDriver()!=null){

						ws.addCell(new Label(3, i + 1, p[i].getDriver().getName()+"", wcf));
					}else{
						ws.addCell(new Label(3, i + 1,"" , wcf));

				}
//			//1微信／2支付宝／3现金
//				if(p[i].getType()!=null){
//					if(p[i].getType()==1){
//						ws.addCell(new Label(4, i + 1, "微信", wcf));
//					}else if (p[i].getType()==2){
//						ws.addCell(new Label(4, i + 1, "支付宝", wcf));
//					} else{
//						ws.addCell(new Label(4, i + 1, "现金", wcf));
//					}
//				}else{
//					ws.addCell(new Label(4, i + 1, "", wcf));
//				}

				if(p[i].getRechargeCode()!=null){
					ws.addCell(new Label(4, i + 1, p[i].getRechargeCode(), wcf));
				}else{
					ws.addCell(new Label(4, i + 1,"", wcf));
				}
				if(p[i].getAmount()!=null) {
						 if(p[i].getRechargeActivity()!=null){
							ws.addCell(new Label(5, i + 1, p[i].getRechargeActivity().getAmount().toString(), wcf));
						}else{
							ws.addCell(new Label(5, i + 1, p[i].getAmount().toString(), wcf));
						}


				}else {
					ws.addCell(new Label(5, i + 1,"", wcf));
				}
				if(p[i].getRechargeActivity()!=null){
					ws.addCell(new Label(6, i + 1, "充"+p[i].getRechargeActivity().getAmount()+"送"+p[i].getRechargeActivity().getGiftAmount(), wcf));
					ws.addCell(new Label(7, i + 1, p[i].getRechargeActivity().getGiftAmount().toString(), wcf));
				}else{
					ws.addCell(new Label(6, i + 1, "", wcf));
					ws.addCell(new Label(7, i + 1, "", wcf));
				}
				ws.addCell(new Label(8, i + 1, p[i].getRemainCash().toString(), wcf));
				if(p[i].getType()!=null){
				if(p[i].getType()==1){
					ws.addCell(new Label(9, i + 1, "微信", wcf));
				}else if (p[i].getType()==2){
					ws.addCell(new Label(9, i + 1, "支付宝", wcf));
				} else if(p[i].getType()==4){
					ws.addCell(new Label(9, i + 1, "现金", wcf));
				}else if(p[i].getType()==5){
					ws.addCell(new Label(9, i + 1, "pos", wcf));
				}else {
					ws.addCell(new Label(9, i + 1, "现金", wcf));
				}
			 	}else{
					ws.addCell(new Label(9, i + 1, "", wcf));
				}
				ws.addCell(new Label(10, i + 1, p[i].getRechargeAdminAccount(), wcf));
				if (i == 0)
					wcf = new WritableCellFormat();


				if(p[i].getReceiptsStatus()!=null){
					if(p[i].getReceiptsStatus().equals("1")){
						ws.addCell(new Label(11, i + 1, "已开票", wcf));
					}else{
						ws.addCell(new Label(11, i + 1, "未开票", wcf));
					}

				}
			}


			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}

	}

	public static void exportRechargeRecoardExcel(String fileName, List<RechargeRecoard> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 10); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(1, 0, "时间", wcf));
			ws.addCell(new Label(2, 0, "摘要", wcf));
			ws.addCell(new Label(3, 0, "增加", wcf));
			ws.addCell(new Label(4, 0, "不变", wcf));
			ws.addCell(new Label(5, 0, "减少", wcf));
			ws.addCell(new Label(6, 0, "余额", wcf));
			ws.addCell(new Label(7, 0, "是否开票", wcf));
//			// 填充数据的内容
			RechargeRecoard[] p = new RechargeRecoard[list.size()];
			for (int i = 0; i < list.size(); i++) {
				p[i] = (RechargeRecoard) list.get(i);
				ws.addCell(new Label(1, i + 1, p[i].getCreateTime().toLocaleString(), wcf));
				if(p[i].getOperType()!=null){
					if(p[i].getOperType()==1){
						ws.addCell(new Label(2, i + 1, "充值", wcf));
					}else if(p[i].getOperType()==2){
						ws.addCell(new Label(2, i + 1, "配载费缴费", wcf));

					}else if(p[i].getOperType()==5){
						ws.addCell(new Label(2, i + 1, "保全金缴费", wcf));

					}else {
						if(p[i].getStatus().equals("1")) {
							ws.addCell(new Label(2, i + 1, "冻结", wcf));
						}else if(p[i].getStatus().equals("2")) {
							ws.addCell(new Label(2, i + 1, "退款", wcf));
						}else{
							ws.addCell(new Label(2, i + 1, "冻结返回", wcf));
						}
					}
				}else{
					ws.addCell(new Label(2, i + 1, "", wcf));
				}


				if(p[i].getOperType()!=null){
					if(p[i].getOperType()==1){
						ws.addCell(new Label(3, i + 1, p[i].getAmount()+"", wcf));
						ws.addCell(new Label(4, i + 1, "0", wcf));
						ws.addCell(new Label(5, i + 1, "0", wcf));
					}else if(p[i].getOperType()==2){
						ws.addCell(new Label(3, i + 1,"0" , wcf));
						ws.addCell(new Label(4, i + 1, p[i].getAmount()+"", wcf));
						ws.addCell(new Label(5, i + 1, "0", wcf));
					}
					else if(p[i].getOperType()==5){
						ws.addCell(new Label(3, i + 1,"0" , wcf));
						ws.addCell(new Label(4, i + 1, "0", wcf));
						ws.addCell(new Label(5, i + 1, p[i].getAmount()+"", wcf));
					}else {
						if(p[i].getStatus().equals("1")) {
							ws.addCell(new Label(3, i + 1,"0" , wcf));
							ws.addCell(new Label(4, i + 1, "0", wcf));
							ws.addCell(new Label(5, i + 1, p[i].getAmount()+"", wcf));
						}else if(p[i].getStatus().equals("2")) {
							ws.addCell(new Label(3, i + 1,"0" , wcf));
							ws.addCell(new Label(4, i + 1, "0", wcf));
							ws.addCell(new Label(5, i + 1, p[i].getAmount()+"", wcf));
						}else{
							ws.addCell(new Label(3, i + 1,"0" , wcf));
							ws.addCell(new Label(4, i + 1, p[i].getAmount()+"", wcf));
							ws.addCell(new Label(5, i + 1, "0", wcf));
						}
					}
				}
//			//1微信／2支付宝／3现金
//				if(p[i].getType()!=null){
//					if(p[i].getType()==1){
//						ws.addCell(new Label(4, i + 1, "微信", wcf));
//					}else if (p[i].getType()==2){
//						ws.addCell(new Label(4, i + 1, "支付宝", wcf));
//					} else{
//						ws.addCell(new Label(4, i + 1, "现金", wcf));
//					}
//				}else{
//					ws.addCell(new Label(4, i + 1, "", wcf));
//				}

				if(p[i].getRemainCash()!=null){
					ws.addCell(new Label(6, i + 1, p[i].getRemainCash().toString(), wcf));
				}else{
					ws.addCell(new Label(6, i + 1,"", wcf));
				}

				if(p[i].getReceiptsStatus()!=null){
					if(p[i].getReceiptsStatus()=="1"){
						ws.addCell(new Label(7, i + 1, "已开票", wcf));
					}else{
						ws.addCell(new Label(7, i + 1, "未开票", wcf));
					}

				}

				if (i == 0)
					wcf = new WritableCellFormat();
			}

			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}






	public static void exportWithdrawApply(String fileName, List<WithdrawApply> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 10); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(1, 0, "申请日期", wcf));
			ws.addCell(new Label(2, 0, "司机姓名", wcf));
			ws.addCell(new Label(3, 0, "手机号", wcf));
			ws.addCell(new Label(4, 0, "身份证号", wcf));
			ws.addCell(new Label(5, 0, "车牌号", wcf));
			ws.addCell(new Label(6, 0, "退款金额", wcf));
			ws.addCell(new Label(7, 0, "账户余额", wcf));
			ws.addCell(new Label(8, 0, "退款银行卡号", wcf));
			ws.addCell(new Label(9, 0, "持卡人姓名", wcf));
			ws.addCell(new Label(10, 0, "退款银行", wcf));
			ws.addCell(new Label(11, 0, "退费原因", wcf));
			ws.addCell(new Label(12, 0, "退款状态", wcf));
			ws.addCell(new Label(13, 0, "操作账户", wcf));
//			// 填充数据的内容
			WithdrawApply[] p = new WithdrawApply[list.size()];
			for (int i = 0; i < list.size(); i++) {
				p[i] = (WithdrawApply) list.get(i);
				ws.addCell(new Label(1, i + 1, DateUtil.date2String(p[i].getCreateTime()), wcf));
				ws.addCell(new Label(2, i + 1, p[i].getDriverName(), wcf));
				ws.addCell(new Label(3, i + 1, p[i].getDriverPhone(), wcf));
				ws.addCell(new Label(4, i + 1, p[i].getIdCardNum(), wcf));
				ws.addCell(new Label(5, i + 1, p[i].getPlateNumber(), wcf));
				ws.addCell(new Label(6, i + 1, p[i].getWithdrawCash().toString(), wcf));
				ws.addCell(new Label(7, i + 1, p[i].getRemainCash().toString(), wcf));
				ws.addCell(new Label(8, i + 1, p[i].getWithdrawBankCardNum(), wcf));
				ws.addCell(new Label(9, i + 1, p[i].getBankCardUserName(), wcf));
				ws.addCell(new Label(10, i + 1, p[i].getBankCardName(), wcf));
				ws.addCell(new Label(11, i + 1, p[i].getWithdrawInfo(), wcf));
				String withdrawInfo="";
				if(p[i].getWithdrawStatus().equals("1")){
					withdrawInfo="审核中";
				}else if(p[i].getWithdrawStatus().equals("2")){
					withdrawInfo="退款成功";
				}else if (p[i].getWithdrawStatus().equals("3")) {
					withdrawInfo="退款失败";
				}
				ws.addCell(new Label(12, i + 1, withdrawInfo, wcf));
				ws.addCell(new Label(13, i + 1, p[i].getOpenUser(), wcf));


				if (i == 0)
					wcf = new WritableCellFormat();
			}

			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}




	public static void exportSecurityListExcel(String fileName, List<RechargeRecoard> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 10); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(1, 0, "日期", wcf));
			ws.addCell(new Label(2, 0, "订单号", wcf));
			ws.addCell(new Label(3, 0, "车牌号", wcf));
			ws.addCell(new Label(4, 0, "收费金额", wcf));
			ws.addCell(new Label(5, 0, "收费员", wcf));
			ws.addCell(new Label(6, 0, "收费方式", wcf));
			ws.addCell(new Label(7, 0, "状态", wcf));
			ws.addCell(new Label(8, 0, "手机号", wcf));

//			// 填充数据的内容
			RechargeRecoard[] p = new RechargeRecoard[list.size()];
			for (int i = 0; i < list.size(); i++) {
				p[i] = (RechargeRecoard) list.get(i);
				ws.addCell(new Label(1, i + 1, DateUtil.date2String(p[i].getCreateTime()), wcf));
				ws.addCell(new Label(2, i + 1, p[i].getOrderCode(), wcf));
				ws.addCell(new Label(3, i + 1, p[i].getPlateNumber(), wcf));
				BigDecimal amount=new BigDecimal(0);
				if(p[i].getAmount()!=null){
					amount=p[i].getAmount();
				}
				ws.addCell(new Label(4, i + 1, amount.toString(), wcf));

				ws.addCell(new Label(5, i + 1, p[i].getCollectorName(), wcf));
				String payMethod="-";
				String payStatus="未收费";
				if(p[i].getSecurityType().equals("2")){
					payMethod="余额";
					 payStatus="已收费";
				}else if(p[i].getSecurityType().equals("3")){
					payMethod="现金";
					payStatus="已收费";
				}
				ws.addCell(new Label(6, i + 1,payMethod, wcf));

				ws.addCell(new Label(7, i + 1, payStatus, wcf));

				ws.addCell(new Label(8, i + 1, p[i].getUserphone(), wcf));

				if (i == 0)
					wcf = new WritableCellFormat();
			}

			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}

	/**
	 * 导出提现申请列表
	 * @param list
	 */
	public static void exportRRWListExcel(String filePath,List<RechargeRecoardWithdrawalVo> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(filePath);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 0); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(0, 0, "序号", wcf));
			ws.addCell(new Label(1, 0, "申请日期", wcf));
			ws.addCell(new Label(2, 0, "司机姓名", wcf));
			ws.addCell(new Label(3, 0, "手机号", wcf));
			ws.addCell(new Label(4, 0, "身份证号", wcf));
			ws.addCell(new Label(5, 0, "可提现金额", wcf));
			ws.addCell(new Label(6, 0, "提现手续费", wcf));
			ws.addCell(new Label(7, 0, "账户余额", wcf));
			ws.addCell(new Label(8, 0, "提现银行卡号", wcf));
			ws.addCell(new Label(9, 0, "持卡人姓名", wcf));
			ws.addCell(new Label(10, 0, "提现银行", wcf));
			ws.addCell(new Label(11, 0, "提现原因", wcf));
			ws.addCell(new Label(12, 0, "上报人", wcf));
			ws.addCell(new Label(13, 0, "运营审核原因", wcf));
			ws.addCell(new Label(14, 0, "运营审核人", wcf));
			ws.addCell(new Label(15, 0, "财务审核原因", wcf));
			ws.addCell(new Label(16, 0, "财务审核人", wcf));
			ws.addCell(new Label(17, 0, "状态", wcf));

//			// 填充数据的内容
			if (list!=null) {
				RechargeRecoardWithdrawalVo rrw=null;
				for (int i = 0; i < list.size(); i++) {
					if (i == 0)
						wcf = new WritableCellFormat();

					rrw=list.get(i);
					ws.addCell(new Label(0, i + 1, String.valueOf(i+1), wcf));
					ws.addCell(new Label(1, i + 1, DateUtil.date2String(rrw.getCreateTime()), wcf));
					ws.addCell(new Label(2, i + 1,rrw.getDriverName() , wcf));
					ws.addCell(new Label(3, i + 1,rrw.getDriverPhone() , wcf));
					ws.addCell(new Label(4, i + 1,rrw.getIdCard() , wcf));

					ws.addCell(new Label(5, i + 1,rrw.getCanWithdrawCash()==null?"0":rrw.getCanWithdrawCash().toString() , wcf));
					ws.addCell(new Label(6, i + 1,rrw.getWithdrawalCash()==null?"0":rrw.getWithdrawalCash().toString() , wcf));
					ws.addCell(new Label(7, i + 1,rrw.getRemainCash()==null?"0":rrw.getRemainCash().toString() , wcf));
					ws.addCell(new Label(8, i + 1,rrw.getBankCard().toString() , wcf));
					ws.addCell(new Label(9, i + 1,rrw.getCardholder() , wcf));
					ws.addCell(new Label(10, i + 1,rrw.getBankName() , wcf));
					ws.addCell(new Label(11, i + 1,rrw.getWithdrawalReason() , wcf));
					ws.addCell(new Label(12, i + 1,rrw.getReportPerson() , wcf));
					ws.addCell(new Label(13, i + 1,rrw.getOperationAuditReason() , wcf));
					ws.addCell(new Label(14, i + 1,rrw.getOperationAssessorUsername() , wcf));
					ws.addCell(new Label(15, i + 1,rrw.getFinancialAuditReason() , wcf));
					ws.addCell(new Label(16, i + 1,rrw.getFinancialAssessorUsername() , wcf));
					ws.addCell(new Label(17, i + 1, WithrawalStatus.getWithrawalStatus(rrw.getStatus().toString()).getName() , wcf));

				}
			}
			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}

	/**
	 * 导出线下保全金列表
	 * @param list
	 */
	public static void exportRechargeOfflineListExcel(String filePath,List<RechargeRecoardOfflineVo> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(filePath);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 0); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(0, 0, "日期", wcf));
			ws.addCell(new Label(1, 0, "订单号", wcf));
			ws.addCell(new Label(2, 0, "手机号", wcf));
			ws.addCell(new Label(3, 0, "车牌号", wcf));
			ws.addCell(new Label(4, 0, "收费金额", wcf));
			ws.addCell(new Label(5, 0, "收费员", wcf));
			ws.addCell(new Label(6, 0, "收费方式", wcf));
			ws.addCell(new Label(7, 0, "状态", wcf));

//			// 填充数据的内容
			if (list!=null) {
				RechargeRecoardOfflineVo vo=null;
				for (int i = 0; i < list.size(); i++) {
					if (i == 0)
						wcf = new WritableCellFormat();
					vo=list.get(i);
					ws.addCell(new Label(0, i + 1, DateUtil.date2String(vo.getCreateTime()), wcf));
					ws.addCell(new Label(1, i + 1, toString(vo.getRechargeCode()), wcf));
					ws.addCell(new Label(2, i + 1,toString(vo.getUserPhone()) , wcf));
					ws.addCell(new Label(3, i + 1,toString(vo.getPlateNumber()) , wcf));
					ws.addCell(new Label(4, i + 1,toString(vo.getAmount()) , wcf));
					ws.addCell(new Label(5, i + 1,toString(vo.getCollectorName()) , wcf));
					ws.addCell(new Label(6, i + 1,toString(vo.getPayType()).equals("4")?"现金":"" , wcf));
					ws.addCell(new Label(7, i + 1,toString(vo.getChargeStatus()).equals("1")?"已支付":"未支付" , wcf));
				}
			}
			wwb.write();
			wwb.close();

		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}

	private static String toString(Object obj){
		if (obj==null) {
			return "";
		}else
			return obj.toString();
	}

	public static void exportDriverListExcel(String fileName, List<DriverDetailVo> list) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet ws = wwb.createSheet("sheet", 0); // 创建一个工作表

			// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(1, 500);
			ws.addCell(new Label(0, 0, "姓名", wcf));
			ws.addCell(new Label(1, 0, "性别", wcf));
			ws.addCell(new Label(2, 0, "创建时间", wcf));
			ws.addCell(new Label(3, 0, "账号", wcf));
			ws.addCell(new Label(4, 0, "驾驶证号", wcf));
			ws.addCell(new Label(5, 0, "道路运输许可证", wcf));
			ws.addCell(new Label(6, 0, "车牌号", wcf));
			ws.addCell(new Label(7, 0, "车型", wcf));
			ws.addCell(new Label(8, 0, "车长", wcf));
			ws.addCell(new Label(9, 0, "载重", wcf));
			ws.addCell(new Label(10, 0, "司机状态", wcf));
			ws.addCell(new Label(11, 0, "车辆状态", wcf));

//			// 填充数据的内容
			DriverDetailVo d=null;
			VehicleVo v=null;
			int rowNum=0;
			for (int i = 0; i < list.size(); i++) {
				d=list.get(i);
				v=null;//重置对象
//				if (i == 0)
//					wcf = new WritableCellFormat();
				//一个司机没有车  只会产生一条数据
				if (d.getVehicleList()==null || d.getVehicleList().size()==0) {
					rowNum++;
					setDriverCell(0, v, d, wcf, ws, rowNum);
				}else{//一个司机多条数据
					for (int j = 0; j < d.getVehicleList().size(); j++) {
						rowNum++;
						setDriverCell(j, v, d, wcf, ws, rowNum);
					}
				}
			}
			wwb.write();
			wwb.close();
		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}

	private static void setDriverCell(int j, VehicleVo v, DriverDetailVo d, WritableCellFormat wcf, WritableSheet ws, int rowNum){
		try {
			if (d.getVehicleList() != null) {
				v = d.getVehicleList().get(j);
			}
			ws.addCell(new Label(0, rowNum, d.getName(), wcf));
			ws.addCell(new Label(1, rowNum, StringUtils.isBlank(d.getSex())?"":(d.getSex().equals("0")?"女":"男"), wcf));
			ws.addCell(new Label(2, rowNum, DateUtil.date2String(d.getCreateTime()), wcf));
			ws.addCell(new Label(3, rowNum, d.getDriverAccount(), wcf));
			ws.addCell(new Label(4, rowNum, d.getDriverLicense(), wcf));
			ws.addCell(new Label(5, rowNum,v==null?"":v.getTransportLicense(), wcf));
			ws.addCell(new Label(6, rowNum, v==null?"":v.getPlateNumber(), wcf));
			ws.addCell(new Label(7, rowNum, v==null?"":v.getVehicleTpye(), wcf));
			ws.addCell(new Label(8, rowNum, v==null?"":v.getVehicleLength()==null?"":v.getVehicleLength().toString(), wcf));
			ws.addCell(new Label(9, rowNum, v==null?"":v.getVehicleLoad()==null?"":v.getVehicleLoad().toString(), wcf));
			ws.addCell(new Label(10, rowNum, DriverStatus.getUserStatus(d.getDriverStatus()).getName(), wcf));
			ws.addCell(new Label(11, rowNum, (v!=null&&v.getWorkStatus()!=null)?DriverWorkStatus.getUserStatus(v.getWorkStatus().toString()).getName():"", wcf));
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public static void exportSecurityAndRoadFeeListExcel(String fileName, Map<String,Object> data) {
		WritableWorkbook wwb;
		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			wwb = Workbook.createWorkbook(fos);
			WritableSheet sheet = wwb.createSheet("sheet", 0); // 创建一个工作表

			//设置字体种类和格式
			WritableFont bold = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
			WritableCellFormat wcfFormat = new WritableCellFormat(bold);
			wcfFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中

			WritableFont dataBold = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD);
			WritableCellFormat dataFormat = new WritableCellFormat(dataBold);
			dataFormat.setAlignment(Alignment.CENTRE);//单元格中的内容水平方向居中


			//合并单元格,合并既可以是横向的，也可以是纵向的
			//这里的第一个数据代表第二列（纵坐标），第二个数据代表第一行（横坐标），第三个数据代表第四列（长），第四个数据代表第二行（高）
			sheet.mergeCells(0, 0, 3, 0);
			sheet.mergeCells(4, 0, 7, 0);
			//单元格是字符串格式！第一个是代表列数,第二是代表行数，第三个代表要写入的内容,第四个代表字体格式  （0代表excel的第一行或者第一列）
			Label label = new Label(0, 0, "配载费",wcfFormat); //这里的（0,0）表示第一行第一列的表格
			sheet.addCell(label);
			label = new Label(4, 0, "保全金",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(0, 1, 1, 1);
			label = new Label(0, 1, "现金",wcfFormat);
			sheet.addCell(label);
			sheet.mergeCells(2, 1, 3, 1);
			label = new Label(2, 1, "余额",wcfFormat);
			sheet.addCell(label);
			sheet.mergeCells(4, 1, 5, 1);
			label = new Label(4, 1, "现金",wcfFormat);
			sheet.addCell(label);
			sheet.mergeCells(6, 1, 7, 1);
			label = new Label(6, 1, "余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(0, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(1, 2, "金额",wcfFormat);
			sheet.addCell(label);
			label = new Label(2, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(3, 2, "金额",wcfFormat);
			sheet.addCell(label);
			label = new Label(4, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(5, 2, "金额",wcfFormat);
			sheet.addCell(label);
			label = new Label(6, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(7, 2, "金额",wcfFormat);
			sheet.addCell(label);

//			roadfeeCashCount
			label = new Label(0, 3, data.get("roadfeeCashCount").toString(),dataFormat);
			sheet.addCell(label);
//			roadfeeCash
			label = new Label(1, 3, data.get("roadfeeCash").toString(),dataFormat);
			sheet.addCell(label);
//			roadfeeBalanceCount
			label = new Label(2, 3, data.get("roadfeeBalanceCount").toString(),dataFormat);
			sheet.addCell(label);
//			roadfeeBalance
			label = new Label(3, 3, data.get("roadfeeBalance").toString(),dataFormat);
			sheet.addCell(label);
//			securityCashCount
			label = new Label(4, 3, data.get("securityCashCount").toString(),dataFormat);
			sheet.addCell(label);
//			securityCash
			label = new Label(5, 3, data.get("securityCash").toString(),dataFormat);
			sheet.addCell(label);
//			securityBalanceCount
			label = new Label(6, 3, data.get("securityBalanceCount").toString(),dataFormat);
			sheet.addCell(label);
//			securityBalance
			label = new Label(7, 3, data.get("securityBalance").toString(),dataFormat);
			sheet.addCell(label);


			sheet.mergeCells(0, 5, 0, 6);
			label = new Label(0, 5, "时间",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(1, 5, 1, 6);
			label = new Label(1, 5, "订单号",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(2, 5, 2, 6);
			label = new Label(2, 5, "手机号",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(3, 5, 3, 6);
			label = new Label(3, 5, "收费类型",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(4, 5, 4, 6);
			label = new Label(4, 5, "收费员姓名",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(5, 5, 5, 6);
			label = new Label(5, 5, "车辆类型",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(6, 5, 6, 6);
			label = new Label(6, 5, "车牌号",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(7, 5, 10, 5);
			label = new Label(7, 5, "配载费",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(11, 5, 14, 5);
			label = new Label(11, 5, "保全金",wcfFormat);
			sheet.addCell(label);

			label = new Label(15, 5, "消费前余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(16, 5, "消费后余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(7, 6, "保全金价格",wcfFormat);
			sheet.addCell(label);

			label = new Label(8, 6, "是否满载",wcfFormat);
			sheet.addCell(label);

			label = new Label(9, 6, "收费方式",wcfFormat);
			sheet.addCell(label);

			label = new Label(10, 6, "收费状态",wcfFormat);
			sheet.addCell(label);

			label = new Label(11, 6, "收费金额",wcfFormat);
			sheet.addCell(label);

			label = new Label(12, 6, "收费员",wcfFormat);
			sheet.addCell(label);

			label = new Label(13, 6, "收费方式",wcfFormat);
			sheet.addCell(label);

			label = new Label(14, 6, "状态",wcfFormat);
			sheet.addCell(label);

			label = new Label(15, 6, "支付前余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(16, 6, "支付后余额",wcfFormat);
			sheet.addCell(label);


//			//设置第2行的高度
//			sheet.setRowView(1,400,false);
//			//设置列宽
			sheet.setColumnView(0, 26);
			sheet.setColumnView(1, 26);
			sheet.setColumnView(2, 26);
			sheet.setColumnView(3, 26);
			sheet.setColumnView(4, 26);
			sheet.setColumnView(5, 26);
			sheet.setColumnView(6, 26);
			sheet.setColumnView(7, 26);
			sheet.setColumnView(8, 26);
			sheet.setColumnView(9, 26);
			sheet.setColumnView(10, 26);
			sheet.setColumnView(11, 26);
			sheet.setColumnView(12, 26);
			sheet.setColumnView(13, 26);
			sheet.setColumnView(14, 26);
			sheet.setColumnView(15, 26);
			sheet.setColumnView(16, 26);


			List<RechargeRecoardDo> rechargeRecoardList= (List<RechargeRecoardDo>) data.get("list");
			if (rechargeRecoardList!=null) {
				RechargeRecoardDo rechargeRecoardDo=new RechargeRecoardDo();
				for (int i = 0; i < rechargeRecoardList.size(); i++) {
					rechargeRecoardDo=rechargeRecoardList.get(i);
					label = new Label(0, i+7, DateUtil.date2String(rechargeRecoardDo.getCreateTime()),dataFormat);
					sheet.addCell(label);
					label = new Label(1, i+7, rechargeRecoardDo.getOrderCode(),dataFormat);
					sheet.addCell(label);
					label = new Label(2, i+7, rechargeRecoardDo.getUserPhone(),dataFormat);
					sheet.addCell(label);
					label = new Label(3, i+7, rechargeRecoardDo.getCarFeeType(),dataFormat);
					sheet.addCell(label);
					label = new Label(4, i+7, rechargeRecoardDo.getCollectorName(),dataFormat);
					sheet.addCell(label);
					label = new Label(5, i+7, rechargeRecoardDo.getCarNarrowType(),dataFormat);
					sheet.addCell(label);
					label = new Label(6, i+7, rechargeRecoardDo.getPlateNumber(),dataFormat);
					sheet.addCell(label);
					label = new Label(7, i+7, rechargeRecoardDo.getPayPrice().toString(),dataFormat);
					sheet.addCell(label);
					label = new Label(8, i+7, rechargeRecoardDo.getIsFullLoad().equals(2)?"是":"否",dataFormat);
					sheet.addCell(label);
					label = new Label(9, i+7, rechargeRecoardDo.getPayType().equals(25)?"余额":"现金",dataFormat);
					sheet.addCell(label);
					label = new Label(10, i+7, rechargeRecoardDo.getOrderStatus().equals(2)?"已付款":"待付款",dataFormat);
					sheet.addCell(label);
					if (rechargeRecoardDo.getOperType().equals(5)) {
						label = new Label(11, i+7, rechargeRecoardDo.getAmount().toString(),dataFormat);
						sheet.addCell(label);
						label = new Label(12, i+7, rechargeRecoardDo.getCollectorName(),dataFormat);
						sheet.addCell(label);
						label = new Label(13, i+7, rechargeRecoardDo.getSecurityType().equals(2)?"余额已付":"现金已付",dataFormat);
						sheet.addCell(label);
						label = new Label(14, i+7, rechargeRecoardDo.getOrderStatus().equals(2)?"已付款":"待付款",dataFormat);
						sheet.addCell(label);
					}
					label = new Label(15, i+7, rechargeRecoardDo.getBeforeRemainCash().toString(),dataFormat);
					sheet.addCell(label);
					label = new Label(16, i+7, rechargeRecoardDo.getAfterRemainCash().toString(),dataFormat);
					sheet.addCell(label);
				}
			}
			wwb.write();
			wwb.close();
		} catch (IOException e) {
		} catch (RowsExceededException e) {
		} catch (WriteException e) {
		}
	}


	//生成excel文件
	public static void  writeExcel() throws IOException{
		try{
			String Divpath = "/Users/xianghan/Desktop/bak/hm";//文件保存路径
			File dirFile = new File(Divpath);
			if(!dirFile.exists()){//文件路径不存在时，自动创建目录
				dirFile.mkdir();
			}
			String path = Divpath+"/test.xls";//文件名字
			//创建一个可写入的excel文件对象
			WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
			//使用第一张工作表，将其命名为“测试”
			WritableSheet sheet = workbook.createSheet("测试", 0);

			//设置字体种类和格式
			WritableFont bold = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
			WritableCellFormat wcfFormat = new WritableCellFormat(bold);
			wcfFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中

			WritableFont dataBold = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD);
			WritableCellFormat dataFormat = new WritableCellFormat(dataBold);
			dataFormat.setAlignment(Alignment.CENTRE);//单元格中的内容水平方向居中


			//合并单元格,合并既可以是横向的，也可以是纵向的
			//这里的第一个数据代表第二列（纵坐标），第二个数据代表第一行（横坐标），第三个数据代表第四列（长），第四个数据代表第二行（高）
			sheet.mergeCells(0, 0, 3, 0);
            sheet.mergeCells(4, 0, 7, 0);
            //单元格是字符串格式！第一个是代表列数,第二是代表行数，第三个代表要写入的内容,第四个代表字体格式  （0代表excel的第一行或者第一列）
            Label label = new Label(0, 0, "配载费",wcfFormat); //这里的（0,0）表示第一行第一列的表格
            sheet.addCell(label);
            label = new Label(4, 0, "保全金",wcfFormat);
            sheet.addCell(label);

            sheet.mergeCells(0, 1, 1, 1);
            label = new Label(0, 1, "现金",wcfFormat);
            sheet.addCell(label);
            sheet.mergeCells(2, 1, 3, 1);
            label = new Label(2, 1, "余额",wcfFormat);
            sheet.addCell(label);
            sheet.mergeCells(4, 1, 5, 1);
            label = new Label(4, 1, "现金",wcfFormat);
            sheet.addCell(label);
            sheet.mergeCells(6, 1, 7, 1);
            label = new Label(6, 1, "余额",wcfFormat);
            sheet.addCell(label);

			label = new Label(0, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(1, 2, "金额",wcfFormat);
			sheet.addCell(label);
			label = new Label(2, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(3, 2, "金额",wcfFormat);
			sheet.addCell(label);
			label = new Label(4, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(5, 2, "金额",wcfFormat);
			sheet.addCell(label);
			label = new Label(6, 2, "笔数",wcfFormat);
			sheet.addCell(label);
			label = new Label(7, 2, "金额",wcfFormat);
			sheet.addCell(label);

//			roadfeeCashCount
			label = new Label(0, 3, "3",dataFormat);
			sheet.addCell(label);
//			roadfeeCash
			label = new Label(1, 3, "134.56",dataFormat);
			sheet.addCell(label);
//			roadfeeBalanceCount
			label = new Label(2, 3, "14",dataFormat);
			sheet.addCell(label);
//			roadfeeBalance
			label = new Label(3, 3, "15",dataFormat);
			sheet.addCell(label);
//			securityCashCount
			label = new Label(4, 3, "16",dataFormat);
			sheet.addCell(label);
//			securityCash
			label = new Label(5, 3, "134.77",dataFormat);
			sheet.addCell(label);
//			securityBalanceCount
			label = new Label(6, 3, "17",dataFormat);
			sheet.addCell(label);
//			securityBalance
			label = new Label(7, 3, "138.56",dataFormat);
			sheet.addCell(label);


			sheet.mergeCells(0, 5, 0, 6);
			label = new Label(0, 5, "时间",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(1, 5, 1, 6);
			label = new Label(1, 5, "订单号",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(2, 5, 2, 6);
			label = new Label(2, 5, "手机号",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(3, 5, 3, 6);
			label = new Label(3, 5, "收费类型",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(4, 5, 4, 6);
			label = new Label(4, 5, "收费员姓名",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(5, 5, 5, 6);
			label = new Label(5, 5, "车辆类型",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(6, 5, 6, 6);
			label = new Label(6, 5, "车牌号",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(7, 5, 10, 5);
			label = new Label(7, 5, "配载费",wcfFormat);
			sheet.addCell(label);

			sheet.mergeCells(11, 5, 14, 5);
			label = new Label(11, 5, "保全金",wcfFormat);
			sheet.addCell(label);

			label = new Label(15, 5, "消费前余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(16, 5, "消费后余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(7, 6, "保全金价格",wcfFormat);
			sheet.addCell(label);

			label = new Label(8, 6, "是否满载",wcfFormat);
			sheet.addCell(label);

			label = new Label(9, 6, "收费方式",wcfFormat);
			sheet.addCell(label);

			label = new Label(10, 6, "收费状态",wcfFormat);
			sheet.addCell(label);

			label = new Label(11, 6, "收费金额",wcfFormat);
			sheet.addCell(label);

			label = new Label(12, 6, "收费员",wcfFormat);
			sheet.addCell(label);

			label = new Label(13, 6, "收费方式",wcfFormat);
			sheet.addCell(label);

			label = new Label(14, 6, "状态",wcfFormat);
			sheet.addCell(label);

			label = new Label(15, 6, "支付前余额",wcfFormat);
			sheet.addCell(label);

			label = new Label(16, 6, "支付后余额",wcfFormat);
			sheet.addCell(label);


//			//设置第2行的高度
//			sheet.setRowView(1,400,false);
//			//设置列宽
			sheet.setColumnView(0, 26);
			sheet.setColumnView(1, 26);
			sheet.setColumnView(2, 26);
			sheet.setColumnView(3, 26);
			sheet.setColumnView(4, 26);
			sheet.setColumnView(5, 26);
			sheet.setColumnView(6, 26);
			sheet.setColumnView(7, 26);
			sheet.setColumnView(8, 26);
			sheet.setColumnView(9, 26);
			sheet.setColumnView(10, 26);
			sheet.setColumnView(11, 26);
			sheet.setColumnView(12, 26);
			sheet.setColumnView(13, 26);
			sheet.setColumnView(14, 26);
			sheet.setColumnView(15, 26);
			sheet.setColumnView(16, 26);




			label = new Label(0, 7, "2018-03-01 11:36:32",dataFormat);
			sheet.addCell(label);

			label = new Label(1, 7, "12311111111111111",dataFormat);
			sheet.addCell(label);

//			sheet.setColumnView(1, 40);

//			//插入图片
//			File file=new File("d:\\test\\123.png");
//			//WritableImage前面四个参数的类型都是double，依次是 x, y, width, height,这里的宽和高可不是图片的宽和高，而是图片所要占的单位格的个数
//			WritableImage image=new WritableImage(1, 3, 1, 3,file);
//			sheet.addImage(image);

			//整型数据
//			Number label2 = new Number(0, 1,31415926);
//			sheet.addCell(label2);
//
//			//添加带有formatting的Number对象
//			NumberFormat nf = new NumberFormat("#.##");
//			WritableCellFormat wcfNF = new WritableCellFormat(nf);
//			Number labelNF = new Number(0, 3, 3.1415926, wcfNF);
//			sheet.addCell(labelNF);
//
//			//boolean型数据
//			Boolean label3 = new Boolean(0,4,true);
//			sheet.addCell(label3);
//
//			//添加DateTime对象
//			DateTime labelDT = new DateTime(0, 5, new Date());
//			sheet.addCell(labelDT);
//
//			//添加带有formatting的DateFormat对象
//			DateFormat df = new DateFormat("yyyy-MM-dd HH:mm:ss"); //HH是24小时制，hh是12小时制
//			WritableCellFormat wcfDF = new WritableCellFormat(df);
//			DateTime labelDTF = new DateTime(4, 1, new Date(), wcfDF);
//			sheet.addCell(labelDTF);

			//关闭对象，释放资源
			workbook.write();
			workbook.close();

		}catch(Exception e){
			e.printStackTrace();

		}

	}

    public static void main(String[] args) throws IOException {
        writeExcel();
    }
}
